package com.peenongsaban.freelance.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.peenongsaban.freelance.bean.AptitudeBean;
import com.peenongsaban.freelance.bean.AptitudeTypeBean;
import com.peenongsaban.freelance.bean.PictureBean;
import com.peenongsaban.freelance.bean.PostJobBean;
import com.peenongsaban.freelance.connection.DatabaseConnectionPool;
import com.peenongsaban.freelance.dao.JobPageDAO;
import com.peenongsaban.freelance.meta.AptitudeMeta;
import com.peenongsaban.freelance.meta.AptitudeOfJobViewMeta;
import com.peenongsaban.freelance.meta.AptitudeTypeMeta;
import com.peenongsaban.freelance.meta.JobMeta;
import com.peenongsaban.freelance.meta.ListPostJobViewMeta;
import com.peenongsaban.freelance.meta.PaymentOfJobMeta;
import com.peenongsaban.freelance.meta.PictureMeta;
import com.peenongsaban.freelance.meta.PostJobMeta;
import com.peenongsaban.freelance.meta.PostJobPromotionMeta;
import com.peenongsaban.freelance.meta.PromotionMeta;
import com.peenongsaban.freelance.meta.UserMeta;
import com.peenongsaban.freelance.meta.ViewMeta;
import com.peenongsaban.freelance.utils.ConstantUtils;
import com.peenongsaban.freelance.utils.ExceptionUtil;

public class JobPageDAOImpl implements JobPageDAO {

	@Override
	public Vector<AptitudeTypeBean> listAptitudeType() {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<AptitudeTypeBean> vector = new Vector<AptitudeTypeBean>();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT * FROM " + AptitudeTypeMeta.TABLE;
			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				AptitudeTypeBean bean = new AptitudeTypeBean();
				bean.setAptitudeTypeID(rs
						.getInt(AptitudeTypeMeta.COL_APTITUDE_TYPE_ID));
				bean.setAptitudeTypeName(rs
						.getString(AptitudeTypeMeta.COL_APTITUDE_TYPE_NAME));
				bean.setImage_icon(rs
						.getString(AptitudeTypeMeta.COL_IMAGE_ICON));
				bean.setDescription(rs
						.getString(AptitudeTypeMeta.COL_DESCRIPTION));
				vector.add(bean);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				if (statement != null)
					statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}

	@Override
	public Vector<PostJobBean> listRecommendedJob() {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<PostJobBean> vector = new Vector<PostJobBean>();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT * FROM " + ViewMeta.LIST_POST_JOB_V
					+ " ORDER BY " + PostJobMeta.COL_AMOUNT_USER_REQUEST_JOB
					+ " DESC LIMIT 0,21";

			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				PostJobBean bean = new PostJobBean();
				bean.setPostID(rs.getString(ListPostJobViewMeta.COL_POST_ID));
				bean.getUser().setIdCard(
						rs.getString(ListPostJobViewMeta.COL_ID_CARD));
				bean.getUser().setTrustLevel(
						rs.getDouble(ListPostJobViewMeta.COL_TRUST_LEVEL));
				bean.setPostDate(rs.getDate(ListPostJobViewMeta.COL_POST_DATE));
				bean.getJob().setJobName(
						rs.getString(ListPostJobViewMeta.COL_JOB_NAME));
				bean.getJob().setDescriptionOfJob(rs
						.getString(ListPostJobViewMeta.COL_DESCRIPTION_OF_JOB));
				bean.getJob().setFormatTime(
						rs.getString(ListPostJobViewMeta.COL_FORMAT_TIME));
				bean.getJob().setWorkArea(
						rs.getString(ListPostJobViewMeta.COL_WORK_AREA));
				bean.getJob().setStartDateOfJob(
						rs.getDate(ListPostJobViewMeta.COL_START_DATE_OF_JOB));
				bean.getJob().setEndDateOfJob(
						rs.getDate(ListPostJobViewMeta.COL_END_DATE_OF_JOB));
				bean.getJob()
						.getPaymentOfJob()
						.setFixPrice(
								rs.getDouble(ListPostJobViewMeta.COL_FIX_PRICE));
				bean.getJob()
						.getPaymentOfJob()
						.setMinPrice(
								rs.getDouble(ListPostJobViewMeta.COL_MIN_PRICE));
				bean.getJob()
						.getPaymentOfJob()
						.setMaxPrice(
								rs.getDouble(ListPostJobViewMeta.COL_MAX_PRICE));
				bean.setAmountRequestPost(rs
						.getInt(ListPostJobViewMeta.COL_AMOUNT_USER_REQUEST_JOB));
				vector.add(bean);
			}

			for (PostJobBean bean : vector) {
				Vector<AptitudeBean> apVector = new Vector<AptitudeBean>();
				statement = null;
				sql = "SELECT * FROM " + AptitudeOfJobViewMeta.VIEW + " WHERE "
						+ AptitudeOfJobViewMeta.COL_POST_ID + " = ?";
				statement = conn.prepareStatement(sql);
				statement.setString(1, bean.getPostID());
				rs = statement.executeQuery();
				while (rs.next()) {
					AptitudeBean ap = new AptitudeBean();
					ap.setAptitudeID(Integer.parseInt(rs
							.getString(AptitudeOfJobViewMeta.COL_APTITUDE_ID)));
					ap.setAptitudeName(rs
							.getString(AptitudeOfJobViewMeta.COL_APTITUDE_NAME));
					ap.setDescription(rs
							.getString(AptitudeOfJobViewMeta.COL_DESCRIPTION));
					ap.getAptitudeType().setAptitudeTypeID(
							rs.getInt(AptitudeOfJobViewMeta.COL_APTITUDE_ID));
					ap.getAptitudeType()
							.setAptitudeTypeName(
									rs.getString(AptitudeOfJobViewMeta.COL_APTITUDE_TYPE_NAME));
					apVector.add(ap);
				}
				bean.setListAptitude(apVector);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			databaseConnection.destroy();
			databaseConnection = null;
			try {
				statement.close();
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}

	@Override
	public Vector<PostJobBean> listUrgentJob() {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<PostJobBean> vector = new Vector<PostJobBean>();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT * FROM " + PostJobPromotionMeta.TABLE
					+ " PRO LEFT JOIN " + ViewMeta.LIST_POST_JOB_V
					+ " LI ON PRO." + PostJobMeta.COL_POST_ID + "=LI."
					+ PostJobMeta.COL_POST_ID + " WHERE PRO."
					+ PromotionMeta.COL_PROMOTION_ID + "="+ConstantUtils.POST_JOB_URGENT_CODE;

			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				PostJobBean bean = new PostJobBean();
				bean.setPostID(rs.getString(PostJobMeta.COL_POST_ID));
				bean.getUser().setIdCard(
						rs.getString(PostJobMeta.COL_ID_CARD));
				bean.getUser().setTrustLevel(
						rs.getDouble(UserMeta.COL_TRUST_LEVEL));
				bean.setPostDate(rs.getDate(PostJobMeta.COL_POST_DATE));
				bean.getJob().setJobID(
						rs.getString(JobMeta.COL_JOB_ID));
				bean.getJob().setJobName(
						rs.getString(JobMeta.COL_JOB_NAME));
				String description = rs
						.getString(JobMeta.COL_DESCRIPTION_OF_JOB);
				bean.getJob().setDescriptionOfJob(description);
				bean.getJob().setFormatTime(
						rs.getString(JobMeta.COL_FORMAT_TIME));
				bean.getJob().setWorkArea(
						rs.getString(JobMeta.COL_WORK_AREA));
				bean.getJob().setStartDateOfJob(
						rs.getDate(JobMeta.COL_START_DATE_OF_JOB));
				bean.getJob().setEndDateOfJob(
						rs.getDate(JobMeta.COL_END_DATE_OF_JOB));
				bean.getJob()
						.getPaymentOfJob()
						.setFixPrice(
								rs.getDouble(PaymentOfJobMeta.COL_FIX_PRICE));
				bean.getJob()
						.getPaymentOfJob()
						.setMinPrice(
								rs.getDouble(PaymentOfJobMeta.COL_MIN_PRICE));
				bean.getJob()
						.getPaymentOfJob()
						.setMaxPrice(
								rs.getDouble(PaymentOfJobMeta.COL_MAX_PRICE));
				bean.setAmountRequestPost(rs
						.getInt(PostJobMeta.COL_AMOUNT_USER_REQUEST_JOB));
				
				// Query picture of job
				Vector<PictureBean> picVector = new Vector<PictureBean>();
				PreparedStatement statementPic = null;
				sql = "SELECT * FROM " + ViewMeta.PICTURE_OF_JOB_V + " WHERE "
						+ JobMeta.COL_JOB_ID + " = ?";
				statementPic = conn.prepareStatement(sql);
				statementPic.setString(1, bean.getJob().getJobID());
				ResultSet rsPic = statementPic.executeQuery();
				while (rsPic.next()) {
					PictureBean pic = new PictureBean();
					pic.setPictureID(rsPic
							.getString(PictureMeta.COL_PICTURE_ID));
					pic.setPictureLocation(rsPic
							.getString(PictureMeta.COL_PICTURE_LOCATION));
					pic.setDescription(rsPic
							.getString(PictureMeta.COL_DESCRIPTION));
					picVector.add(pic);
				}
				bean.getJob().setListPicture(picVector);
				
				
				vector.add(bean);
			}

			for (PostJobBean bean : vector) {
				Vector<AptitudeBean> apVector = new Vector<AptitudeBean>();
				statement = null;
				sql = "SELECT * FROM " + AptitudeOfJobViewMeta.VIEW + " WHERE "
						+ AptitudeOfJobViewMeta.COL_POST_ID + " = ?";
				statement = conn.prepareStatement(sql);
				statement.setString(1, bean.getPostID());
				rs = statement.executeQuery();
				while (rs.next()) {
					AptitudeBean ap = new AptitudeBean();
					ap.setAptitudeID(Integer.parseInt(rs
							.getString(AptitudeMeta.COL_APTITUDE_ID)));
					ap.setAptitudeName(rs
							.getString(AptitudeMeta.COL_APTITUDE_NAME));
					ap.setDescription(rs
							.getString(AptitudeMeta.COL_DESCRIPTION));
					ap.getAptitudeType().setAptitudeTypeID(
							rs.getInt(AptitudeMeta.COL_APTITUDE_ID));
					ap.getAptitudeType()
							.setAptitudeTypeName(
									rs.getString(AptitudeTypeMeta.COL_APTITUDE_TYPE_NAME));
					apVector.add(ap);
				}
				bean.setListAptitude(apVector);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			databaseConnection.destroy();
			databaseConnection = null;
			try {
				statement.close();
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}

}
